﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SqliteTest1
{
    public class SQLiteDataAdapterWrapper : IDisposable
    {
        private readonly SQLiteConnection _connection;
        private readonly SQLiteCommand _command;
        private SQLiteDataAdapter _adapter;

        public SQLiteDataAdapterWrapper(string connectionString, string commandText)
        {
            _connection = new SQLiteConnection(connectionString);
            _connection.Open();
            _command = new SQLiteCommand(commandText, _connection);
            _adapter = new SQLiteDataAdapter(_command);
            var builder = new SQLiteCommandBuilder(_adapter);
        }

        public DataSet ExecuteQueryAndFillDataSet(string tableName)
        {
            var dataSet = new DataSet();
            _adapter.Fill(dataSet, tableName);
            return dataSet;
        }

        public int UpdateDataSet(DataSet dataSet, string tableName)
        {
            int updatedRows = _adapter.Update(dataSet, tableName);
            return updatedRows;
        }

        public void Dispose()
        {
            _adapter?.Dispose();
            _command?.Dispose();
            _connection?.Close();
            _connection?.Dispose();
        }

        public int UpdateDataSetWithTransaction(DataSet dataSet, string tableName)
        {
            // 开始一个事务  
            using (var transaction = _connection.BeginTransaction())
            {
                try
                {
                    // 尝试更新数据库  
                    int updatedRows = _adapter.Update(dataSet, tableName);
                    // 提交事务  
                    transaction.Commit();
                    return updatedRows;
                }
                catch (Exception ex)
                {
                    // 如果发生异常，回滚事务  
                    transaction.Rollback();
                    // 你可以选择记录异常信息或将其抛出  
                    // throw; // 如果需要上层处理异常，可以取消注释这一行  
                    return 0; // 或者返回一个表示失败的数字  
                }
            }
        }

        private DataSet FilterEmptyRowsExceptId(DataSet dataSet, string tableName)
        {
            DataTable dataTable = dataSet.Tables[tableName];
            List<DataRow> rowsToRemove = new List<DataRow>();
            dataTable.AcceptChanges();
            foreach (DataRow row in dataTable.Rows.Cast<DataRow>().Where(x => x.RowState != DataRowState.Detached).ToList())
            {
                bool isOnlyIdFilled = true;
                foreach (DataColumn column in dataTable.Columns)
                {
                    if (column.ColumnName != "id" && row[column] == DBNull.Value)
                    {
                        // 如果除了id之外的列有空值，标记此行需要删除  
                        isOnlyIdFilled = false;
                        break;
                    }
                }

                if (!isOnlyIdFilled)
                {
                    rowsToRemove.Add(row);
                }
            }

            foreach (DataRow row in rowsToRemove)
            {
                var index = dataTable.Rows.IndexOf(row);
                if (index != -1)
                {
                    var dataRow = dataTable.Rows[index];
                    dataRow.Delete();
                }
            }


            return dataSet;
        }
    }
}
